package com.ithiem.dubbo.mappers;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.itheima.model.domain.Activity;
import com.itheima.model.domain.DashUser;
import com.itheima.model.domain.DashUserForSql;
import com.itheima.model.domain.Summary;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface ActivityMapper extends BaseMapper<Activity> {
//    查询相同的记录
    @Select("select * from tb_activity where user_id = #{userId} and  activity_date = #{nowDate} and activity_date = #{activityType}")
    Activity queryActivityByUserId(Long userId, String nowDate, String activityType);

//    查询今天的指定类型的活动总数---登录
    @Select("select COUNT(*)AS newUsersToday from tb_activity where activity_date = #{nowDate} and activity_type = #{activityType}")
    Summary queryActivity(@Param("nowDate") String nowDate, @Param("activityType") String activityType);

    @Select("select COUNT(DISTINCT  user_id)AS activeUsersToday from tb_activity where activity_date = #{nowDate}")
    Summary queryActiveUserCounts(@Param("nowDate") String nowDate);

    @Select("select activity_date,MAX(updated)AS updated,MIN(created)AS created from tb_activity WHERE  activity_date = #{activityDate} ")
    Activity queryActivityByDesc(@Param("activityDate") String activityDate);
    @Select("select * from tb_activity where user_id = #{userId} order by updated desc limit 1")
    Activity queryOne(Long userId);
    @Select("SELECT MONTH(created)AS title ,COUNT(DISTINCT user_id)AS amount FROM tb_activity WHERE YEAR(created)=#{year} AND activity_type=#{activityType} GROUP BY MONTH(created)")
    List<DashUserForSql> queryByDateRange(@Param("year") Integer year , @Param("activityType") String activityType);

    // 查找每月的活跃用户---按时间查找->只要用户有记录即可--任意活动
    @Select("SELECT MONTH(created)AS title ,COUNT(DISTINCT user_id)AS amount FROM tb_activity WHERE YEAR(created)=#{year} GROUP BY MONTH(created)")
    List<DashUserForSql> queryByLastDateRange(@Param("year") Integer year );
//  次日留存
    @Select("SELECT COUNT(DISTINCT n.user_id) AS amount FROM ( SELECT user_id , min(date) as first_day  FROM tb_activity where YEAR(created) =#{year} and MONTH(created)=#{month} GROUP BY user_id ) a  LEFT JOIN tb_activity n  ON a.user_id = n.user_id  and  DATEDIFF(n.date,a.first_day ) = 1 ")
    DashUser queryActivieForMonth(@Param("year") Integer year, @Param("month")Integer month);
    //查询指定创建时间的用户的年龄分布
    @Select("SELECT CASE WHEN (age >= 0 AND age <= 17) THEN '0-17岁' WHEN (age >= 18 AND age <= 23)  THEN '18-23岁' WHEN (age >= 24 AND age <= 30)  THEN '24-30岁' WHEN (age >= 31 AND age <= 40)  THEN '31-40岁' WHEN (age >= 41 AND age <= 50)  THEN '41-50岁' WHEN (age >= 51 AND age <= 200)   THEN '50岁+' END 'title',count(*) amount\n" +
            "FROM tb_user_info WHERE created >=#{startDate} and created <=#{endDate}\n" +
            "GROUP BY CASE WHEN (age >= 0 AND age <= 17) THEN '0-17岁' WHEN (age >= 18 AND age <= 23) THEN '18-23岁' WHEN (age >= 24 AND age <= 30) THEN '24-30岁'\n" +
            "WHEN (age >= 31 AND age <= 40) THEN '31-40岁' WHEN (age >= 41 AND age <= 50) THEN '41-50岁' WHEN (age >= 51 AND age <= 200)  THEN '50岁+' END ORDER BY 1;")
    List<DashUser> queryAgeRangeByDate(@Param("startDate") String startDate, @Param("endDate")String endDate);
    //查询指定创建时间的用户的行业分布前十名
@Select("select profession as title, COUNT(*) amount FROM tb_user_info WHERE created >=#{startDate} and created <=#{endDate} GROUP BY profession ORDER BY amount desc LIMIT 10")
    List<DashUser> queryWorkRangeByDate(@Param("startDate")String startDate, @Param("endDate") String endDate);
    //查询指定创建时间的用户的性别分布
    @Select("SELECT gender as title,COUNT(*) AS amount FROM tb_user_info WHERE created >=#{startDate} and created <=#{endDate} GROUP BY gender")
    List<DashUser> querySexRangeByDate(@Param("startDate")String startDate, @Param("endDate") String endDate);
    //查询指定创建时间的用户的地区分布
    @Select("select city AS title,COUNT(*) AS amount FROM tb_user_info WHERE created >=#{startDate} and created <=#{endDate} GROUP BY city")
    List<DashUser> queryLocalRangeByDate(@Param("startDate")String startDate, @Param("endDate") String endDate);





   /* @Select("select * form tb_activity where user_id = #{userId} order by update limit 1")
    Activity queryBestNewDate(Long userId);*/
}
